package com.github.shiqiyue.app.modules.dbms.service.impl;

import com.github.shiqiyue.app.api.sapi.controller.dbms.dto.DataQueryDTO;
import com.github.shiqiyue.app.api.sapi.controller.dbms.dto.TableFieldInfoDTO;
import com.github.shiqiyue.app.api.sapi.controller.dbms.dto.TableInfoDTO;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import com.github.shiqiyue.app.common.exception.BusinessException;
import com.github.shiqiyue.app.modules.dbms.rowmapper.TableFieldInfoRowMapper;
import com.github.shiqiyue.app.modules.dbms.service.IDatabaseService;
import com.github.shiqiyue.app.modules.dbms.service.IDbQueryService;
import com.github.shiqiyue.app.modules.dbms.service.IUserQueryService;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Limit;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/***
 * 数据库查询
 * @author wenyao.wu
 * @date 2019/8/9
 */
@Slf4j
@Service
public class DbQueryServiceImpl implements IDbQueryService {

    @Autowired
    private IDatabaseService databaseService;

    @Autowired
    private IUserQueryService userQueryService;


    @Override
    public List<TableInfoDTO> getTablelInfoByDatabaseId(Long databaseId) {
        JdbcTemplate jdbcTemplate = databaseService.getJdbcTemplateByDatabaseId(databaseId);
        String sql = "select COLUMN_NAME,TABLE_NAME, COLUMN_COMMENT,COLUMN_DEFAULT,IS_NULLABLE,COLUMN_TYPE,COLUMN_KEY,EXTRA from information_schema.columns where table_schema =database()";
        List<TableFieldInfoDTO> fields = jdbcTemplate.query(sql, new TableFieldInfoRowMapper());
        if (CollectionUtils.isEmpty(fields)) {
            return Collections.EMPTY_LIST;
        }
        Map<String, TableInfoDTO> tableInfoMap = Maps.newHashMap();
        for (TableFieldInfoDTO field : fields) {
            TableInfoDTO tableInfo = tableInfoMap.get(field.getTableName());
            if (tableInfo == null) {
                tableInfo = new TableInfoDTO();
                tableInfo.setTableName(field.getTableName());
                tableInfo.setFields(Lists.newArrayList());
                tableInfoMap.put(field.getTableName(), tableInfo);
            }
            tableInfo.addField(field);
        }
        return Lists.newArrayList(tableInfoMap.values());
    }

    @Override
    public DataQueryDTO executeSelect(Long userId, Long databaseId, String sql) {
        JdbcTemplate jdbcTemplate = databaseService.getJdbcTemplateByDatabaseId(databaseId);
        if (!isSelect(sql)) {
            throw new BusinessException("仅支持select");
        }
        userQueryService.addRecord(databaseId, userId, sql);
        try {
            sql = getLimitSql(sql);
        } catch (JSQLParserException e) {
            log.error("获取limit sql失败", e);
            throw new BusinessException("获取limit sql 失败");
        }
        Long startTime = System.currentTimeMillis();
        log.info("dbms执行sql:{}", sql);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        Long useTime = System.currentTimeMillis() - startTime;
        List columnNames = Lists.newArrayList();
        if (org.apache.commons.collections.CollectionUtils.isNotEmpty(list)) {
            columnNames = Arrays.asList(list.get(0).keySet().toArray());
        }
        DataQueryDTO queryDTO = new DataQueryDTO();
        queryDTO.setUseTime(useTime);
        queryDTO.setColumnNames(columnNames);
        queryDTO.setData(list);
        return queryDTO;
    }

    @Override
    public DataQueryDTO analysisSql(Long id, Long databaseId, String sql) {
        JdbcTemplate jdbcTemplate = databaseService.getJdbcTemplateByDatabaseId(databaseId);
        if (!isSelect(sql)) {
            throw new BusinessException("仅支持select");
        }
        sql = "desc " + sql;
        Long startTime = System.currentTimeMillis();
        log.info("dbms执行sql:{}", sql);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        Long useTime = System.currentTimeMillis() - startTime;
        List columnNames = Lists.newArrayList();
        if (org.apache.commons.collections.CollectionUtils.isNotEmpty(list)) {
            columnNames = Arrays.asList(list.get(0).keySet().toArray());
        }
        DataQueryDTO queryDTO = new DataQueryDTO();
        queryDTO.setUseTime(useTime);
        queryDTO.setColumnNames(columnNames);
        queryDTO.setData(list);
        return queryDTO;
    }

    /***
     * 判断sql是否是select语句
     * @param sql
     * @return
     */
    private Boolean isSelect(String sql) {
        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            if (statement instanceof Select) {
                return true;
            }
            return null;
        } catch (Exception e) {
            log.error("sql解析异常", e);
            return null;
        }
    }

    private String getLimitSql(String sql) throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(sql);
        if (statement instanceof Select) {
            Select select = (Select) statement;
            SelectBody selectBody = select.getSelectBody();
            if (selectBody instanceof PlainSelect) {
                PlainSelect plainSelect = (PlainSelect) selectBody;
                // 如果没有Limit,设置默认limit为 0, 1000
                if (plainSelect.getLimit() == null) {
                    Limit limit = new Limit();
                    limit.setOffset(new LongValue(0));
                    limit.setRowCount(new LongValue(1000));
                    plainSelect.setLimit(limit);
                } else {
                    // 如果有Limit,则判断rowCount是否大于1000，大于1000则设置rowcount为1000
                    Limit limit = plainSelect.getLimit();
                    if (((LongValue) limit.getRowCount()).getValue() > 1000) {
                        limit.setRowCount(new LongValue(1000));
                    }
                }

            }
        }
        return statement.toString();
    }


}
